#!/bin/bash

################################################################################
# 精子银行管理系统 - 数据库重置脚本
# Sperm Bank Management System - Database Reset Script
#
# ⚠️  警告：这会删除所有现有数据并重新创建数据库！
# ⚠️  WARNING: This will DELETE ALL existing data!
#
# 使用方法：
#   chmod +x reset-db.sh
#   ./reset-db.sh
#
################################################################################

set -e  # 遇到错误立即退出

# ========== 颜色定义 ==========
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# ========== 配置 ==========
PROJECT_DIR="/var/www/sperm-bank_-db"
BACKEND_DIR="$PROJECT_DIR/backend-sperm-bank"
SQL_DIR="$BACKEND_DIR/sql"
BACKEND_SERVICE="sperm-bank-api"
DB_NAME="hospital_db"
DB_USER="hospital_user"

# ========== 工具函数 ==========
print_header() {
    echo -e "\n${BLUE}========================================${NC}"
    echo -e "${BLUE}$1${NC}"
    echo -e "${BLUE}========================================${NC}\n"
}

print_success() { echo -e "${GREEN}✓ $1${NC}"; }
print_error() { echo -e "${RED}✗ $1${NC}"; }
print_warning() { echo -e "${YELLOW}⚠ $1${NC}"; }

# ========== 警告确认 ==========
clear
echo -e "${RED}"
echo "╔════════════════════════════════════════════════════════╗"
echo "║                   ⚠️  警告 WARNING ⚠️                   ║"
echo "║                                                        ║"
echo "║  这将删除数据库中的所有数据！                          ║"
echo "║  This will DELETE ALL data in the database!           ║"
echo "╚════════════════════════════════════════════════════════╝"
echo -e "${NC}"

read -p "确定要继续吗？输入 yes 继续: " confirmation

if [ "$confirmation" != "yes" ]; then
    echo -e "${YELLOW}操作已取消${NC}"
    exit 0
fi

# ========== 开始重置 ==========
echo -e "${GREEN}"
echo "╔════════════════════════════════════════════════════════╗"
echo "║             开始重置数据库...                          ║"
echo "╚════════════════════════════════════════════════════════╝"
echo -e "${NC}"

# 1. 停止后端服务
print_header "[1/7] 停止后端服务"
if sudo systemctl stop "$BACKEND_SERVICE" 2>/dev/null; then
    print_success "后端服务已停止"
else
    print_warning "后端服务未运行或停止失败（继续）"
fi

# 2. 断开所有数据库连接
print_header "[2/7] 断开所有数据库连接"
sudo -u postgres psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();" 2>/dev/null || print_warning "无活动连接或断开失败（继续）"
print_success "数据库连接已断开"

# 3. 删除旧数据库
print_header "[3/7] 删除旧数据库"
if sudo -u postgres psql -c "DROP DATABASE IF EXISTS $DB_NAME;" 2>&1 | tee /tmp/drop_db.log; then
    print_success "旧数据库已删除（如果存在）"
else
    print_error "删除数据库失败"
    cat /tmp/drop_db.log
    exit 1
fi

# 4. 创建新数据库
print_header "[4/7] 创建新数据库和用户"

# 创建用户（如果不存在）
sudo -u postgres psql -c "DO \$\$ BEGIN IF NOT EXISTS (SELECT FROM pg_user WHERE usename = '$DB_USER') THEN CREATE USER $DB_USER WITH PASSWORD 'hospital123'; END IF; END \$\$;" 2>/dev/null || true

# 创建数据库
if sudo -u postgres psql -c "CREATE DATABASE $DB_NAME OWNER $DB_USER;"; then
    print_success "新数据库已创建: $DB_NAME (所有者: $DB_USER)"
else
    print_error "创建数据库失败"
    exit 1
fi

# 授予数据库权限
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;"
print_success "数据库权限已授予"

# 5. 执行SQL脚本（正确的顺序：表→约束→触发器→数据）
print_header "[5/7] 执行SQL脚本"

echo "  [1/4] 创建表结构..."
if sudo -u postgres psql -d "$DB_NAME" -f "$SQL_DIR/01_create_tables.sql" > /tmp/sql_01.log 2>&1; then
    print_success "  ✓ 表结构创建完成"
else
    print_error "  ✗ 表结构创建失败"
    cat /tmp/sql_01.log
    exit 1
fi

echo "  [2/4] 创建约束..."
if sudo -u postgres psql -d "$DB_NAME" -f "$SQL_DIR/03_create_constraints.sql" > /tmp/sql_03.log 2>&1; then
    print_success "  ✓ 约束创建完成"
else
    print_error "  ✗ 约束创建失败"
    cat /tmp/sql_03.log
    exit 1
fi

echo "  [3/4] 创建触发器和函数..."
if sudo -u postgres psql -d "$DB_NAME" -f "$SQL_DIR/04_create_triggers_function.sql" > /tmp/sql_04.log 2>&1; then
    print_success "  ✓ 触发器创建完成"
else
    print_error "  ✗ 触发器创建失败"
    cat /tmp/sql_04.log
    exit 1
fi

echo "  [4/4] 插入样本数据..."
if sudo -u postgres psql -d "$DB_NAME" -f "$SQL_DIR/02_insert_sample_data.sql" > /tmp/sql_02.log 2>&1; then
    print_success "  ✓ 样本数据插入完成"
else
    print_error "  ✗ 样本数据插入失败"
    cat /tmp/sql_02.log
    exit 1
fi

# 授予表和序列的权限给应用用户
echo "  授予表和序列权限..."
sudo -u postgres psql -d "$DB_NAME" -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $DB_USER;" > /dev/null 2>&1
sudo -u postgres psql -d "$DB_NAME" -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $DB_USER;" > /dev/null 2>&1
sudo -u postgres psql -d "$DB_NAME" -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DB_USER;" > /dev/null 2>&1
sudo -u postgres psql -d "$DB_NAME" -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DB_USER;" > /dev/null 2>&1
print_success "  ✓ 表和序列权限已授予 $DB_USER"

# 6. 验证数据库
print_header "[6/7] 验证数据库"

TABLE_COUNT=$(sudo -u postgres psql -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
TABLE_COUNT=$(echo $TABLE_COUNT | xargs)  # 去除空格
print_success "数据库表数量: $TABLE_COUNT"

DONOR_COUNT=$(sudo -u postgres psql -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM donors;")
DONOR_COUNT=$(echo $DONOR_COUNT | xargs)
print_success "捐精人数量: $DONOR_COUNT"

RECIPIENT_COUNT=$(sudo -u postgres psql -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM recipients;")
RECIPIENT_COUNT=$(echo $RECIPIENT_COUNT | xargs)
print_success "接收方数量: $RECIPIENT_COUNT"

VIAL_COUNT=$(sudo -u postgres psql -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM sperm_vials;")
VIAL_COUNT=$(echo $VIAL_COUNT | xargs)
print_success "样本数量: $VIAL_COUNT"

ORDER_COUNT=$(sudo -u postgres psql -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM orders;")
ORDER_COUNT=$(echo $ORDER_COUNT | xargs)
print_success "订单数量: $ORDER_COUNT"

# 7. 重启后端服务
print_header "[7/7] 重启后端服务"
if sudo systemctl start "$BACKEND_SERVICE"; then
    print_success "后端服务已启动"
    sleep 2
    if sudo systemctl is-active --quiet "$BACKEND_SERVICE"; then
        print_success "后端服务运行正常"
    else
        print_error "后端服务启动失败"
        echo ""
        echo "查看服务状态："
        sudo systemctl status "$BACKEND_SERVICE" --no-pager
        echo ""
        echo "查看最近日志："
        sudo journalctl -u "$BACKEND_SERVICE" -n 20 --no-pager
        exit 1
    fi
else
    print_error "后端服务启动失败"
    exit 1
fi

# 完成
echo -e "\n${GREEN}╔════════════════════════════════════════════════════════╗${NC}"
echo -e "${GREEN}║              ✓ 数据库重置完成！                        ║${NC}"
echo -e "${GREEN}╚════════════════════════════════════════════════════════╝${NC}\n"

echo -e "${BLUE}数据库信息：${NC}"
echo "  数据库名称: $DB_NAME"
echo "  数据库用户: $DB_USER"
echo "  表数量: $TABLE_COUNT"
echo "  捐精人: $DONOR_COUNT 位"
echo "  接收方: $RECIPIENT_COUNT 位"
echo "  样本: $VIAL_COUNT 个"
echo "  订单: $ORDER_COUNT 个"
echo ""
echo -e "${YELLOW}提示：${NC}请在浏览器中刷新页面查看新数据"
echo ""

# 清理临时日志文件
rm -f /tmp/sql_*.log /tmp/drop_db.log 2>/dev/null || true
